﻿Imports System.Data.SqlClient
Public Class InventarioDAL
#Region "Incluir/Alterar"
    Public Function inseriralterar(inv_codigo As Integer, inv_data As Nullable(Of DateTime), sta_codigo As Integer, usu_codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "execute SPInventario @inv_codigo, @inv_data, @sta_codigo, @usu_codigo"
            cmd.Parameters.Add(New SqlParameter("@inv_codigo", SqlDbType.Int)).Value = inv_codigo
            If (inv_data.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@inv_data", SqlDbType.DateTime)).Value = inv_data
            Else
                cmd.Parameters.Add(New SqlParameter("@inv_data", SqlDbType.DateTime)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@sta_codigo", SqlDbType.Int)).Value = sta_codigo
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usu_codigo
            codigo = objconexao.executacmdAI(cmd, "Ofi_Inventario")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Excluir"
    Public Function excluir(inv_codigo As Integer, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "delete from Estoque.Ofi_Inventario where inv_codigo=@inv_codigo"
            cmd.Parameters.Add(New SqlParameter("@inv_codigo", SqlDbType.Int)).Value = inv_codigo
            codigo = objconexao.executaCmd(cmd)
            objfuncoes.gravaocorrencias("Exclusão de compras", inv_codigo, 47, usuario)
        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer, descricao2 As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 1
                    'por codigo
                    cmd.CommandText = "select * from vw_EstInventario where  inv_codigo= @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 2
                    'por status
                    cmd.CommandText = "select * from vw_EstInventario where  sta_codigo= @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)

                Case 3
                    'dt

                    cmd.CommandText = "select * from VW_EstInventario where  inv_data between @codigo and @codigo2"
                    descricao += " 00:00:00"
                    descricao2 += " 23:59:59"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.DateTime)).Value = FormatDateTime(descricao, DateFormat.GeneralDate)
                    cmd.Parameters.Add(New SqlParameter("@codigo2", SqlDbType.DateTime)).Value = FormatDateTime(descricao2, DateFormat.GeneralDate)
                Case Else
                    'todos
                    cmd.CommandText = "select * from vw_EstInventario where sta_codigo=4"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Atualizar pra 0 restante"
    Public Function atualizar(inv_codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand

            cmd.CommandText = "update Estoque.Ofi_Peca set pec_qtde=0 where pec_codigo not in( select pec_codigo from Estoque.Ofi_InventarioItens where inv_codigo=@codigo)"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = inv_codigo
            tab = objconexao.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
